package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.ContentInfo;
import cn.com.checne.model.domain.UserInfo;
import cn.com.checne.model.domain.WebsiteInfo;

@Repository
public class ContentInfoDao {
	protected static final Logger logger = Logger
			.getLogger(ContentInfoDao.class);

	public boolean insert(ContentInfo content) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "insert into tbl_content (content_id,content_title,content_websiteid_fk,content_text,content_publishTime,content_userid_fk) values(?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i, content.getContentId());
			i++;
			pstmt.setString(i, content.getTitle());
			i++;
			pstmt.setInt(i, content.getWebsiteId());
			i++;
			pstmt.setString(i, content.getText());
			i++;
			pstmt.setString(i, content.getPublishTime());
			i++;
			pstmt.setInt(i, content.getUserId());

			pstmt.execute();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<ContentInfo> getSubList(ContentInfo content, int start, int num) {
		List<ContentInfo> list = new ArrayList<ContentInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_content  where 1=1 ";
			if (StringUtils.isNotBlank(content.getKw())) {
				sql = sql
						+ " and (content_title like ? or content_text like ?) ";
			}
			if (content.getWebsiteId() > 0) {
				sql = sql + " and content_websiteid_fk = ? ";
			}
			if (content.getUserId() > 0) {
				sql = sql + " and content_userid_fk = ? ";
			}
			sql = sql + " order by content_publishTime desc limit ? , ?";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (StringUtils.isNotBlank(content.getKw())) {
				i++;
				pstmt.setString(i, "%" + content.getKw() + "%");
				i++;
				pstmt.setString(i, "%" + content.getKw() + "%");
			}
			if (content.getWebsiteId() > 0) {
				i++;
				pstmt.setInt(i, content.getWebsiteId());
			}
			if (content.getUserId() > 0) {
				i++;
				pstmt.setInt(i, content.getUserId());
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);
			ResultSet rs = pstmt.executeQuery();
			list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public int getCount(ContentInfo content) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_content  where 1=1 ";
			if (StringUtils.isNotBlank(content.getKw())) {
				sql = sql
						+ " and (content_title like ? or content_text like ?) ";
			}
			if (content.getWebsiteId() > 0) {
				sql = sql + " and content_websiteid_fk = ? ";
			}
			if (content.getUserId() > 0) {
				sql = sql + " and content_userid_fk = ? ";
			}
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (StringUtils.isNotBlank(content.getKw())) {
				i++;
				pstmt.setString(i, "%" + content.getKw() + "%");
				i++;
				pstmt.setString(i, "%" + content.getKw() + "%");
			}
			if (content.getWebsiteId() > 0) {
				i++;
				pstmt.setInt(i, content.getWebsiteId());
			}
			if (content.getUserId() > 0) {
				i++;
				pstmt.setInt(i, content.getUserId());
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("total");
			}
			pstmt.close();
			conn.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public ContentInfo getContentById(String contentId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_content where content_id = ? ";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setString(i, contentId);
			ResultSet rs = pstmt.executeQuery();
			List<ContentInfo> list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return (list != null) && (list.size() > 0) ? (ContentInfo) list
					.get(0) : new ContentInfo();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new ContentInfo();
	}

	public boolean update(ContentInfo content) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_content set content_title = ? , content_websiteid_fk = ? , content_text = ? , content_publishTime = ? ,content_userid_fk = ? where content_id = ? ";

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setString(i, content.getTitle());
			i++;
			pstmt.setInt(i, content.getWebsiteId());
			i++;
			pstmt.setString(i, content.getText());
			i++;
			pstmt.setString(i, content.getPublishTime());
			i++;
			pstmt.setInt(i, content.getUserId());
			i++;
			pstmt.setString(i, content.getContentId());

			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean del(String contentId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "delete from  tbl_content where content_id = ? ";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setString(i, contentId);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	private List<ContentInfo> getListFromRS(ResultSet rs) {
		List<ContentInfo> list = new ArrayList<ContentInfo>();
		ContentInfo content = null;
		try {
			while (rs.next()) {
				content = new ContentInfo();
				content.setContentId(rs.getString("content_id"));
				content.setTitle(rs.getString("content_title"));
				content.setWebsiteId(rs.getInt("content_websiteid_fk"));
				content.setText(rs.getString("content_text"));
				content.setPublishTime(rs.getString("content_publishTime"));
				content.setUserId(rs.getInt("content_userid_fk"));

				WebsiteInfo websiteInfo = new WebsiteInfoDao()
						.getWebsite(content.getWebsiteId());
				content.setWebsiteInfo(websiteInfo);

				UserInfo userInfo = new UserInfoDao().getUser(content
						.getUserId());
				content.setUserInfo(userInfo);

				list.add(content);
			}
			rs.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}
}